/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dao.impl;

import dao.ReporteDao;
import entity.Reporte;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

/**
 *
 * @author USER
 */
public class ReporteDaoImpl implements ReporteDao {

    private DriverManagerDataSource dataSource;

    public void setDataSource(DriverManagerDataSource dataSource) {
        this.dataSource = dataSource;
    }

    @Override
    public List<Reporte> ReporteEfectividad() {
        List<Reporte> list = null;
        String sql = "select t.codtor, t.destor, t.canpar, t.fecreg, (select count(*) from mkt_proyecto_detalle m where t.codtor=m.codtor) participantes_reales from mkt_proyecto_torneo t";
        Connection cn = null;

        try {
            cn = dataSource.getConnection();
            Statement st = cn.createStatement();
            ResultSet rs = st.executeQuery(sql);
            list = new ArrayList<Reporte>();
            while (rs.next()) {
                Reporte Reporte = new Reporte();
                Reporte.setCodtor(rs.getDouble(1));
                Reporte.setDestor(rs.getString(2));
                Reporte.setCanpar(rs.getDouble(3));
                Reporte.setFecreg(rs.getString(4));
                Integer participantesReales = rs.getInt(5);
                Double efectividad = (participantesReales * 100) / rs.getDouble(3);
                Reporte.setPorefe(efectividad); 
                list.add(Reporte);
            }
        } catch (SQLException e) {
            list = null;
        } finally {
            try {
                cn.close();
            } catch (Exception e) {
            }
        }
        return list;
    }
}
